﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Gpsys.Entity.MSG;
using Gpsys.Entity.Dapper;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using Gpsys.Logger;

namespace Gpsys.Web.Business
{
    /// <summary>
    /// QueueBusiness
    /// </summary>
    public class QueueBusiness : BaseBusiness
    {
        const string SELECT_FIELDS = @"[Id]
                                          ,[Type]
                                          ,[Flow]
                                          ,[TRN]
                                          ,[Currency]
                                          ,[Amount]
                                          ,[RelatedQueueId]
                                          ,[LastLog]
                                          ,[CreatedBy]
                                          ,[CreatedOn]
                                          ,[ModifiedBy]
                                          ,[ModifiedOn]
                                          ,[NextWorkflow]
                                          ,[ServerId]
                                        ";
        /// <summary>
        /// Initializes a new instance of the <see cref="QueueBusiness" /> class.
        /// </summary>
        public QueueBusiness()
        {

        }

        /// <summary>
        /// Gets the queue XML.
        /// </summary>
        /// <param name="qId">The q id.</param>
        /// <returns></returns>
        public QueueXml GetQueueXml(long qId)
        {
            try
            {
                SQL.OpenConnection();

                var qxml = SQL.Query<QueueXml>(@"SELECT [QueueId]
                                                      ,[Xml]
                                                      ,[ModifiedBy]
                                                      ,[ModifiedOn]
                                                  FROM [QueueXml]
                                                  WHERE [QueueId] =  @Id", new { Id = qId }).SingleOrDefault();

                return qxml;

            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                SQL.CloseConnection();
            }
        }

        /// <summary>
        /// Gets the queue.
        /// </summary>
        /// <param name="Id">The id.</param>
        /// <returns></returns>
        public Queue GetQueue(long Id)
        {
            try
            {
                SQL.OpenConnection();

                return SQL.Query<Queue>(string.Format(@"select {0}
                                        from [Queue]
                                        Where [Id] = @Id ", SELECT_FIELDS), new { Id = Id }).SingleOrDefault();

            }
            catch (Exception e)
            {
                Log.WriteError("GetQueue : " + e.Message);

                return null;
            }
            finally
            {
                SQL.CloseConnection();
            }

        }

        /// <summary>
        /// Gets the queue log list.
        /// </summary>
        /// <param name="Id">The id.</param>
        /// <returns></returns>
        public QueueLogList GetQueueLogList(long Id)
        {
            try
            {
                SQL.OpenConnection();
                QueueLogList qll = new QueueLogList();
                var ql = SQL.Query<QueueLog>(@"select [Id]
                                                  ,[QueueId]
                                                  ,[LogContent]
                                                  ,[CreatedOn]
                                                  ,[Createdby]
                                                from [QueueLog]
                                                Where [QueueId] = @Id ", new { Id = Id }).ToList();

                qll.AddRange(ql);

                return qll;

            }
            catch (Exception e)
            {
                Log.WriteError("GetQueue : " + e.Message);

                return null;
            }
            finally
            {
                SQL.CloseConnection();
            }

        }

        /// <summary>
        /// Gets the queues for pagination.
        /// </summary>
        /// <param name="model">The model.</param>
        /// <returns></returns>
        public List<Queue> GetQueuesForPagination(QueueTablePaginationRequest model)
        {
            List<string> columns = new List<string>()
            {
                "[Id]",
                "[Type]",
                "[Flow]",
                "[TRN]",
                "[Currency]",
                "[Amount]",
                "[LastLog]",
                "[ModifiedOn]"
            };
            string whereClause = "";

            if (!string.IsNullOrEmpty(model.sSearch))
            {
                model.sSearch = string.Format("%{0}%", model.sSearch);
                whereClause = " where [TRN] like @trn ";
            }

            List<Queue> qs = new List<Queue>();
            try
            {
                SQL.OpenConnection();

                var sql = string.Format(@"select top {2} {4}
                                    from (
	                                    select ROW_NUMBER() OVER (ORDER BY {1} {0}) as rownum
		                                      ,{4}
	                                    from [Queue]
                                        {3}
                                        ) as TableA
                                    where rownum between @start and @start + @top
                                    ",
                                     model.sSortDir_0.ToLower() == "asc" ? "DESC" : "ASC",
                                     columns[model.iSortCol_0],
                                     model.iDisplayLength,
                                     whereClause,
                                     SELECT_FIELDS);

                qs = SQL.Query<Queue>(sql, new { top = model.iDisplayLength, start = model.iDisplayStart == 0 ? 0 : model.iDisplayStart + 1, trn = model.sSearch }).ToList();

                var sqlCount = string.Format("select count(1) from [Queue] {0}", whereClause);
                model.TotalRow = SQL.Query<int>(sqlCount, new { trn = model.sSearch }).SingleOrDefault();


            }
            catch (Exception e)
            {
                Log.WriteError("GetQueuesForPagination : " + e.Message);
            }
            finally
            {
                SQL.CloseConnection();
            }

            return qs;
        }

    }
}
